--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_AMOUNT_DEPF.HQL
--    Functions : 表41：存款发生额分额度统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_AMOUNT_DEPF PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4                      AS FIN_ORG_DIST
       ,XT.CCY                                AS CCY
       ,XT.INTERVAL_CODE2                     AS INTERVAL_CODE2  -- 产品类别
       ,XT.INTERVAL_DESC2                     AS INTERVAL_DESC2  -- 产品类别描述
       ,SUM(XT.OCCUR_AMOUNT )/100000000       AS OCCUR_AMOUNT    -- 存款发生额
       ,SUM(XT.AMOUNT_LM     )/100000000      AS AMOUNT_LM       -- 发生额上期末
       ,SUM(XT.AMOUNT_YS     )/100000000      AS AMOUNT_YS       -- 当年累计发生额
       ,SUM(XT.AMOUNT_GROW   )/100000000      AS AMOUNT_GROW     -- 同比增长
       ,SUM(XT.WSUM_BAL   )/100000000         AS WSUM_BAL        -- 发生额加权值
       ,SUM(XT.WAMOUNT_LM    )/100000000      AS WAMOUNT_LM      -- 发生额加权值上期末
       ,SUM(XT.WAMOUNT_YS    )/100000000      AS WAMOUNT_YS      -- 当年累计发生额加权值
       ,SUM(XT.WAMOUNT_LC    )/100000000      AS WAMOUNT_LC      -- 发生额加权值去年同期
FROM (
    -- 存款余额区间分类
    SELECT
         DEPF.FIN_ORG_NO                                              AS FIN_ORG_NO
        ,DEPF.CCY                                                     AS CCY
        ,INTER.DEPT_INTERVAL_ID                                       AS INTERVAL_CODE2     -- 存款余额区间分类
        ,INTER.DEPT_INTERVAL_DSCR                                     AS INTERVAL_DESC2     -- 存款余额区间描述
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                               AS OCCUR_AMOUNT       -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                   AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                   AS AMOUNT_YS          -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)    AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                    AS WSUM_BAL           -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                  AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                  AS WAMOUNT_YS         -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                  AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#')DEPF
    -- 与金额区间表关联
    LEFT JOIN DIMENSION.DIM_DEPOSIT_AMOUNT_INTERVAL INTER ON DEPF.INTERVAL_CODE2=INTER.DEPT_INTERVAL_ID AND '#V_DATA_DATE#' BETWEEN INTER.START_DATE AND INTER.END_DATE AND INTER.IS_VALID='1'

    UNION ALL

    -- 合计
    SELECT
         DEPF.FIN_ORG_NO                                              AS FIN_ORG_NO
        ,DEPF.CCY                                                     AS CCY
        ,'HJ'                                                         AS INTERVAL_CODE2     -- 存款余额区间分类
        ,'合计'                                                       AS INTERVAL_DESC2     -- 存款余额区间描述
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                               AS OCCUR_AMOUNT       -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                   AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                   AS AMOUNT_YS          -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)    AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                    AS WSUM_BAL           -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                  AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                  AS WAMOUNT_YS         -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                  AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#')DEPF
    )XT
   -- 与地区表最细级关联
   LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON XT.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
   LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
   GROUP BY   AREA.AREA_CODE_4
             ,XT.CCY
             ,XT.INTERVAL_CODE2
             ,XT.INTERVAL_DESC2;